package com.cennavi.mineplay.utils;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import no.ecc.vectortile.VectorTileEncoder;

import org.springframework.jdbc.core.JdbcTemplate;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.mercator.TileUtils;
import com.vividsolutions.jts.geom.Coordinate;
import com.vividsolutions.jts.geom.CoordinateSequences;
import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.geom.LineString;
import com.vividsolutions.jts.io.WKTReader;

public class DBUtils {

	public static JSONObject getMetaInfoByTableName(String tableName,
			Connection conn) throws Exception {
		// Class.forName("org.postgresql.Driver");

		// Connection conn =
		// DriverManager.getConnection("jdbc:postgresql://36.111.84.170:5442/mineplay",
		// "postgres", "cennavi2018");

		String sql = "select * from " + tableName + " limit 1";

		Statement stmt = conn.createStatement();

		ResultSet rs = stmt.executeQuery(sql);

		ResultSetMetaData md = rs.getMetaData();

		int columnCount = md.getColumnCount();

		StringBuilder sb = new StringBuilder("select ");

		Map<String, Map<String, Object>> map = new HashMap<String, Map<String, Object>>();

		String geomColumnName = null;

		for (int i = 1; i <= columnCount; i++) {
			String cname = md.getColumnName(i);
			int ctype = md.getColumnType(i);
			String ctypename = md.getColumnTypeName(i);

			Map<String, Object> m = new HashMap<String, Object>();

			if ("serial".equals(ctypename) || ctypename.indexOf("int") >= 0
					|| ctypename.indexOf("float") >= 0 || "numeric".equals(ctypename)) {
				m.put("type", "float");
				map.put(cname, m);
			} else if (ctype != 1111) {
				m.put("type", "string");
				map.put(cname, m);
			} else {
				geomColumnName = cname;
			}

			if (map.containsKey(cname)) {
				sb.append(" max(");
				sb.append(cname);
				sb.append(") as max_");
				sb.append(cname);
				sb.append(", ");

				sb.append(" min(");
				sb.append(cname);
				sb.append(") as min_");
				sb.append(cname);
				sb.append(",");
			}

		}

		rs.close();

		double minLng = 180, maxLng = 0, minLat = 90, maxLat = 0;

		String geomSql = "select st_astext(" + geomColumnName
				+ ") as wkt from " + tableName + " limit 30";

		stmt = conn.createStatement();

		rs = stmt.executeQuery(geomSql);
		
		String geotype =null;

		while (rs.next()) {
			String wkt = rs.getString("wkt");

			Geometry geom = new WKTReader().read(wkt);
			
			if (geotype == null) {
				if (geom.getGeometryType().toLowerCase().indexOf("point") >= 0) {
					geotype = "Point";
				} else if (geom.getGeometryType().toLowerCase().indexOf("line") >= 0) {
					geotype = "LineString";
				} else {
					geotype = "Polygon";
				}
			}
			Coordinate[] cs = geom.getCoordinates();

			for (Coordinate c : cs) {
				if (c.x > maxLng)
					maxLng = c.x;
				if (c.x < minLng)
					minLng = c.x;
				if (c.y > maxLat)
					maxLat = c.y;
				if (c.y < minLat)
					minLat = c.y;
			}
		}

		rs.close();
		
		sb.append("count(*) as count from ");
		sb.append(tableName);

		String metaSql = sb.toString();

		rs = stmt.executeQuery(metaSql);
		
		int count = 0;

		while (rs.next()) {
			Iterator<String> it = map.keySet().iterator();

			while (it.hasNext()) {
				String key = it.next();

				Object objMax = rs.getObject("max_" + key);
				Object objMin = rs.getObject("min_" + key);
				map.get(key).put("max", objMax);
				map.get(key).put("min", objMin);
			}
			
			count = rs.getInt("count");
		}

		rs.close();
		stmt.close();

		JSONObject json = new JSONObject();

		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		Iterator<Entry<String, Map<String, Object>>> it = map.entrySet()
				.iterator();

		while (it.hasNext()) {
			Entry<String, Map<String, Object>> en = it.next();

			Map<String, Object> mapv = en.getValue();
			mapv.put("name", en.getKey());

			list.add(mapv);
		}

		json.put("properties", list);
		json.put("count", count);
		json.put("sourceLayer", "position");
		json.put("bounds", new double[][]{new double[]{minLng,minLat},new double[]{maxLng,maxLat}});
		json.put("geometry", geotype);

		return json;
	}

	public static byte[] getProtoBuf(JdbcTemplate jdbc, String sql, int x,
			int y, int z) throws Exception {

		String tile = TileUtils.parseXyz2Bound(x, y, z);
		List<Map<String, Object>> results = jdbc.queryForList(sql, tile);

		VectorTileEncoder vte = new VectorTileEncoder(4096, 16, false);

		for (Map<String, Object> m : results) {
			String wkt = (String) m.get("geom1");
			
			int hashCode = wkt.hashCode();

			Geometry geom = new WKTReader().read(wkt);

			TileUtils.convert2Piexl(x, y, z, geom);

			if (m.containsKey("geom"))
				m.remove("geom");
			
			
			m.remove("geom1");
			
			
			Iterator<Entry<String,Object>> it = m.entrySet().iterator();
			
			while(it.hasNext()){
//				BigDecimal
				
				Entry<String,Object> en = it.next();
				
				String key = en.getKey();
				Object value = en.getValue();
				if (value instanceof BigDecimal)
					m.put(key, ((BigDecimal)value).doubleValue());
			}
			
			m.put("tips_id", String.valueOf(hashCode));

			vte.addFeature("position", m, geom);

		}

		return vte.encode();
	}
	
	
	
	
	public static byte[] getParticleProtoBuf(JdbcTemplate jdbc, String sql, int x,
			int y, int z,int speed,int range) throws Exception {

		String tile = TileUtils.parseXyz2Bound(x, y, z);
		List<Map<String, Object>> results = jdbc.queryForList(sql, tile);

		VectorTileEncoder vte = new VectorTileEncoder(4096, 16, false);

		for (Map<String, Object> m : results) {
			String wkt = (String) m.get("geom1");

			Geometry geom = new WKTReader().read(wkt);
			
			Coordinate[] cs = geom.getCoordinates();
			
			List<List<double[]>> lcs = new ArrayList<>();
			
			int step = range * 4;
			
			List<Geometry> geoms = new ArrayList<>();
			
			for(int i=0;i<cs.length-1;i++){
				Coordinate current = cs[i];
				Coordinate next = cs[i+1];
				
				double step_lng = (next.x - current.x) / step;
				double step_lat = (next.y - current.y) / step;
				
				List<double[]> points = new ArrayList<>();
				
				points.add(new double[]{current.x,current.y});
				
				for(int j=1;j<step;j++){
					double lng = current.x + (j * step_lng);
					double lat = current.y + (j * step_lat);
					points.add(new double[]{lng,lat});
				}
				
				points.add(new double[]{next.x,next.y});
				
				lcs.add(points);
			}
			
			List<double[]> ps = new ArrayList<>();
			
			for(int i=0;i<lcs.size();i++){
				List<double[]> points = lcs.get(i);
				
				if (i ==0 ){
					ps.add(points.get(0));
				}
				
				for(int j=1;j<points.size();j++){
					ps.add(points.get(j));
				}
			}
			
			for(int i=0;i<ps.size()-3;i++){
				String particleLine = "LineString(";
				
				particleLine += ps.get(i)[0] +" " + ps.get(i)[1] +",";
				particleLine += ps.get(i+1)[0] +" " + ps.get(i+1)[1] +",";
				particleLine += ps.get(i+2)[0] +" " + ps.get(i+2)[1] +",";
				particleLine += ps.get(i+3)[0] +" " + ps.get(i+3)[1] +")";
				
				geoms.add(new WKTReader().read(particleLine));
			}
			
			
			
			

			

			if (m.containsKey("geom"))
				m.remove("geom");
			
			
			m.remove("geom1");
			
			
			Iterator<Entry<String,Object>> it = m.entrySet().iterator();
			
			while(it.hasNext()){
//				BigDecimal
				
				Entry<String,Object> en = it.next();
				
				String key = en.getKey();
				Object value = en.getValue();
				if (value instanceof BigDecimal)
					m.put(key, ((BigDecimal)value).doubleValue());
			}
			
			
			for(int i=0;i<geoms.size();i++){
				Geometry g = geoms.get(i);
				
				TileUtils.convert2Piexl(x, y, z, g);
				
//				Map<String,Object> m2 = new HashMap<>();
//				
//				m2.put("id", id++);
//				m2.put("link_seq", i);
//				//m2.put("speed", speed);
//				m2.put("status", speed);
				m.put("link_seq", i);
				m.put("status", speed);
				vte.addFeature("position", m, g);
			}
			
			
			

//			vte.addFeature("position", m, geom);

		}

		return vte.encode();
	}
	
	
	
	

	public static Map<String, Object> getGeojson(JdbcTemplate jdbc, String sql)
			throws Exception {

		Map<String, Object> map = new HashMap<String, Object>();

		map.put("type", "FeatureCollection");

		List<Map<String, Object>> results = jdbc.queryForList(sql);

		List<Map<String, Object>> features = new ArrayList<Map<String, Object>>();

		for (Map<String, Object> m : results) {
			
			int hashCode = 0;
			
			if (m.containsKey("geom")) {
				hashCode = m.get("geom").hashCode();
				m.remove("geom");
			}
			Map<String, Object> feature = new HashMap<String, Object>();
			
			feature.put("type", "Feature");
			Map<String, Object> properties = new HashMap<String, Object>();
			properties.put("tips_id", String.valueOf(hashCode));
			Iterator<Entry<String, Object>> it = m.entrySet().iterator();
			while (it.hasNext()) {
				Entry<String, Object> en = it.next();
				if (!"geojson".equals(en.getKey())) {
					properties.put(en.getKey(), en.getValue());
				}
			}
			feature.put("properties", properties);
			feature.put("geometry", JSON.parse(m.get("geojson").toString()));
			features.add(feature);

		}
		map.put("features", features);

		return map;

	}
	
	
	
	public static Map<String, Object> getChartJson(JdbcTemplate jdbc, String sql,String name)
			throws Exception {

		Map<String, Object> map = new HashMap<String, Object>();

		map.put("title", name);
		
		List<Map<String,Object>> series = new ArrayList<Map<String,Object>>();

		List<Map<String, Object>> results = jdbc.queryForList(sql);

		Map<String,Object> row = new HashMap<String,Object>();
		
		row.put("name", name);
		row.put("data", results);
		series.add(row);
		map.put("series", series);

		return map;

	}
	
	public static Map<String, Object> getListJson(JdbcTemplate jdbc, String sql,String name)
			throws Exception {

		Map<String, Object> map = new HashMap<String, Object>();

		map.put("title", name);
		
		List<Map<String, Object>> results = jdbc.queryForList(sql);
		
		map.put("data", results);

		return map;

	}
	

	public static void insertServiceMeta(Connection conn, String tableName,
			String url, String desc, String type) throws Exception {

		String meta = DBUtils.getMetaInfoByTableName(tableName, conn)
				.toString();

		String sql = "insert into service_meta(describe,url,data_type,meta_info) values (?,?,?,?)";

		PreparedStatement pstmt = conn.prepareStatement(sql);

		pstmt.setString(1, desc);
		pstmt.setString(2, url);
		pstmt.setString(3, type);
		pstmt.setString(4, meta);
		pstmt.execute();

		pstmt.close();

	}
	
	
	public static String getTableCreateSql(Connection conn,String sourceTableName,String destTableName) throws Exception{
		String sql = "select * from "+sourceTableName+" limit 0";
		
		Statement stmt = conn.createStatement();
		
		ResultSet rs = stmt.executeQuery(sql);
		
		ResultSetMetaData md = rs.getMetaData();
		
		StringBuilder sb = new StringBuilder("create table "+destTableName+"(");
		
		for(int i=1;i<=1;i++){
			sb.append(md.getColumnName(i));
			sb.append(" ");
			sb.append(md.getColumnTypeName(i));
			if ("varchar".equals(md.getColumnTypeName(i))){
				sb.append("(");
				sb.append(md.getPrecision(i));
				sb.append(")");
			}
		}
		
		for(int i=2;i<=md.getColumnCount();i++){
			sb.append(",");
			sb.append(md.getColumnName(i));
			sb.append(" ");
			sb.append(md.getColumnTypeName(i));
			if ("varchar".equals(md.getColumnTypeName(i))){
				sb.append("(");
				sb.append(md.getPrecision(i));
				sb.append(")");
			}
		}
		sb.append(")");
		
		rs.close();
		
		stmt.close();
		
		return sb.toString();
	}
	
	
	public static void createTable(String sql,Connection conn) throws Exception{
		
		Statement stmt = conn.createStatement();
		
		stmt.execute(sql);
		
		stmt.close();
		
	}
	
	public static void createSpatialIndex(String tableName,Connection conn) throws Exception{
		
		String sql = "create index idx_geom_"+tableName+" on "+ tableName+" using gist(geom)";
		
		Statement stmt = conn.createStatement();
		
		stmt.execute(sql);
		
		stmt.close();
	}
	
	
	public static Connection getConnection(String driverClass,String jdbc,String user,String password) throws Exception
	{
		Class.forName("org.postgresql.Driver");
		
		Connection conn = DriverManager.getConnection(jdbc, user, password);
		
		return conn;
	}
	
	
	
	public static long submitTask(String param,String describe,Connection conn) throws Exception{
		
		long ids = new Date().getTime();
		
		String status ="运行中";
		
		String sql = "insert into import_task(ids,describe,status,param) values (?,?,?,?)";
		
		PreparedStatement pstmt = conn.prepareStatement(sql);
		
		pstmt.setLong(1, ids);
		pstmt.setString(2, describe);
		pstmt.setString(3, status);
		pstmt.setString(4, param);
		
		pstmt.execute();
		pstmt.close();
		return ids;
	}
	
	public static void finishTask(long ids,Connection conn) throws Exception{
		
		 
		
		String sql = "update import_task set status=? where ids=?";
		
		PreparedStatement pstmt = conn.prepareStatement(sql);
		
		
		pstmt.setString(1, "完成");
		pstmt.setLong(2, ids);
		
		pstmt.executeUpdate();
		pstmt.close();
	}
	
	
	
	public static void ImportData(Connection connSource,Connection connDest,String sourceTable,String targetTable,String param,String describe) throws Exception{
		
		long ids = submitTask(param, describe, connDest);
		
		String createTableSql = DBUtils.getTableCreateSql(connSource, sourceTable, targetTable);
		
		DBUtils.createTable(createTableSql, connDest);
		
		DBUtils.createSpatialIndex(targetTable, connDest);
		
		String sql = "select * from "+ sourceTable;
		
		Statement stmt = connSource.createStatement();
		
		ResultSet rs = stmt.executeQuery(sql);
		
		rs.setFetchSize(5000);
		
		String insertSql = "insert into "+targetTable+" values (?";
		
		int columnCount = rs.getMetaData().getColumnCount();
		
		for(int i=1;i<columnCount;i++)
			insertSql +=",?";
		insertSql += ")";
		
		System.out.println(insertSql);
		
		PreparedStatement pstmt = connDest.prepareStatement(insertSql);
		
		int num = 0;
		
		while(rs.next()){
			for(int i=1;i<=columnCount;i++){
				pstmt.setObject(i, rs.getObject(i));
			}
			
			pstmt.addBatch();
			
			num++;
			
			if (num % 3000 ==0 ){
				pstmt.executeBatch();
			}
				
		}
		
		pstmt.executeBatch();
		
		rs.close();
		stmt.close();
		
		pstmt.close();
		
		finishTask(ids, connDest);
		
		connSource.close();
		connDest.close();
	
		
	}
	
	
	/**
	 * 根据jdbc获取所有表
	 * @param args
	 * @throws Exception
	 */
	public static List<String> getAllTables(String param) throws Exception{
		List<String> tables = new ArrayList<String>();
		
		JSONObject json = JSON.parseObject(param);
		
		Class.forName("org.postgresql.Driver");
		
		String jdbc = json.getString("jdbc");
		
		String username = json.getString("username");
		String password = json.getString("password");
		
		Connection conn = DriverManager.getConnection(jdbc, username, password);
		
		String sql = "select tablename from pg_tables where schemaname='public' and tablename not in ('service_meta','spatial_ref_sys')";
		
		Statement stmt = conn.createStatement();
		
		ResultSet rs = stmt.executeQuery(sql);
		
		while(rs.next()){
			tables.add(rs.getString("tablename"));
		}
		rs.close();
		stmt.close();
		conn.close();
		
		return tables;
	}
	
	
	
	
	
	

//	public static void main(String[] args) throws Exception {
//		Class.forName("org.postgresql.Driver");
//
//		Connection conn = DriverManager.getConnection(
//				"jdbc:postgresql://117.156.126.7:10006/postgres", "postgres",
//				"superman");
//		
////		System.out.println(getMetaInfoByTableName("od", conn));
//		
//		String tableName ="beijing_poi";
//		String url = "http://117.156.126.7:10003/demo/beijing_poi/{z}/{x}/{y}";
//		String desc = "北京兴趣点POI分布";
//		String type = "PBF";
//		insertServiceMeta(conn, tableName, url, desc, type);
//		
//		tableName ="beijing_link";
//		url = "http://117.156.126.7:10003/demo/beijing_link/{z}/{x}/{y}";
//		desc = "北京市城际高速道路";
//		type = "PBF";
//		insertServiceMeta(conn, tableName, url, desc, type);
//
//
//		tableName ="beijing_building";
//		url = "http://117.156.126.7:10003/demo/beijing_building/{z}/{x}/{y}";
//		desc = "北京市房屋建筑分布";
//		type = "PBF";
//		insertServiceMeta(conn, tableName, url, desc, type);
//
//
//		tableName ="beijing_paichusuo";
//		url = "http://117.156.126.7:10003/demo/paichusuo/{z}/{x}/{y}";
//		desc = "北京主要派出所可达分布面";
//		type = "PBF";
//		insertServiceMeta(conn, tableName, url, desc, type);
//
//		tableName ="beijing_paichusuo_ring";
//		url = "http://117.156.126.7:10003/demo/paichusuo_line/{z}/{x}/{y}";
//		desc = "北京主要派出所可达等高线";
//		type = "PBF";
//		insertServiceMeta(conn, tableName, url, desc, type);
//
//		tableName ="od";
//		url = "http://117.156.126.7:10003/demo/od";
//		desc = "省会航线分布";
//		type = "GEOJSON";
//		insertServiceMeta(conn, tableName, url, desc, type);
//		
//		
//		
//		conn.close();
//	}
	
	
//	public static void main(String[] args) throws Exception {
//		Class.forName("org.postgresql.Driver");
//
//		Connection conn = DriverManager.getConnection(
//				"jdbc:postgresql://36.111.84.170:5442/mineplay", "postgres",
//				"cennavi2018");
// 
//		
//		String tableName ="beijing_poi";
//		String url = "http://36.111.84.170:8003/demo/beijing_poi/{z}/{x}/{y}";
//		String desc = "北京兴趣点POI分布";
//		String type = "PBF";
//		insertServiceMeta(conn, tableName, url, desc, type);
//		
//		tableName ="beijing_link";
//		url = "http://36.111.84.170:8003/demo/beijing_link/{z}/{x}/{y}";
//		desc = "北京市城际高速道路";
//		type = "PBF";
//		insertServiceMeta(conn, tableName, url, desc, type);
//
//
//		tableName ="beijing_building";
//		url = "http://36.111.84.170:8003/demo/beijing_building/{z}/{x}/{y}";
//		desc = "北京市房屋建筑分布";
//		type = "PBF";
//		insertServiceMeta(conn, tableName, url, desc, type);
//
//
//		tableName ="beijing_paichusuo";
//		url = "http://36.111.84.170:8003/demo/paichusuo/{z}/{x}/{y}";
//		desc = "北京主要派出所可达分布面";
//		type = "PBF";
//		insertServiceMeta(conn, tableName, url, desc, type);
//
//		tableName ="beijing_paichusuo_ring";
//		url = "http://36.111.84.170:8003/demo/paichusuo_line/{z}/{x}/{y}";
//		desc = "北京主要派出所可达等高线";
//		type = "PBF";
//		insertServiceMeta(conn, tableName, url, desc, type);
//
//		tableName ="od";
//		url = "http://36.111.84.170:8003/demo/od";
//		desc = "省会航线分布";
//		type = "GEOJSON";
//		insertServiceMeta(conn, tableName, url, desc, type);
//		
//		
//		
//		conn.close();
//	}
	
	
//	public static void main(String[] args) throws Exception {
//		Class.forName("org.postgresql.Driver");
//
//		Connection conn = DriverManager.getConnection(
//				"jdbc:postgresql://36.111.84.170:5442/mineplay", "postgres",
//				"cennavi2018");
//		
//		Connection connSource = DriverManager.getConnection(
//				"jdbc:postgresql://117.156.126.7:10006/postgres", "postgres",
//				"superman");
//		
//		String sql = getTableCreateSql(connSource, "gps_c","gps_c");
//		
//		createTable(sql, conn);
//		
//		createSpatialIndex("gps_c", conn);
//		
//		ImportData(connSource, conn, "gps_c","gps_c");
//	}

}
